create table code_master(
	code_no number primary key,
	code_type varchar2(50) not null,
	code_code varchar2(50) not null,
	code_name varchar2(200) not null
);
drop table code_master;

create sequence code_seq;
drop sequence code_seq;

SELECT * FROM CODE_MASTER;

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','A000','성별');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','B000','회원등급');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','C000','과목');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','D000','학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','E000','학력');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A001','남자');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A002','여자');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'A000','A003','무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B001','학생');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B002','선생님');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'B000','B003','무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C101','국어');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C201','인문계 수학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C202','자연계 수학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C301','영어');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C401','물리');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C402','화학');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C403','생물');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C501','한국사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C502','한국지리');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'C000','C503','경제');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D101','초등학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D102','초등학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D103','초등학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D104','초등학교 4학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D105','초등학교 5학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D106','초등학교 6학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D107','초등학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D201','중학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D202','중학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D203','중학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D204','중학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D301','고등학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D302','고등학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D303','고등학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D304','고등학교 무관');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D401','재수생');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D402','일반인');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'D000','D501','전체 무관');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E101','대학교 1학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E102','대학교 2학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E103','대학교 3학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E104','대학교 4학년');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E201','학사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E202','석사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E203','박사');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'E000','E204','전문선생님');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'0000','F000','비밀번호 질문');

insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G001','자신의 보물 제 1호는?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G002','다시 태어나면 되고 싶은 것은?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G003','가장 기억에 남는 선생님의 성함은?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G004','초등학교 때 기억에 남는 짝꿍 이름은?');
insert into code_master(code_no,code_type,code_code,code_name) values(code_seq.nextval,'G000','G005','인생 깊게 읽은 책 이름은?');



create table notice(
	no number primary key,
	title varchar2(200) not null,
	dateposted date not null,
	writer varchar2(50) not null,
	hits number default 0,
	orgfilename varchar2(200),
	savfilename varchar2(200),
	content varchar2(3000) not null
);
drop table notice;

create sequence notice_seq nocache;
drop sequence notice_seq;

insert into notice(no,title,dateposted,writer,content) values(notice_seq.nextval,'테스트',sysdate,'관리자','테스트용 입니다.');

select * from notice;

create table help(
	no number primary key,
	id varchar2(50) not null,
	title varchar2(200) not null,
	dateposted date not null,
	hits number default 0,
	content varchar2(3000) not null,
	ref number default 0,
	restep number default 0,
	relevel number default 0
);
drop table help;

create sequence help_seq nocache;
drop sequence help_seq;

insert into help(no,id,title,content,dateposted) 
values(help_seq.nextval,'admin','테스트','테스트입니다.',sysdate);

select * from help;

select count(*) from addresscode where sido='전북'


--Member (Teacher, Student)
drop sequence board_spring_seq;
select * from wmt_teacher;
select * from WMT_STUDENT;
drop table wmt_student;

create table wmt_teacher(
 	teacherid varchar2(50) primary key, 
	name varchar2(50) not null,
	password varchar2(50) not null,
	gender varchar2(50) not null,
	tel varchar2(50) not null,
	hoperegion varchar2(50) not null,
	email varchar2(50) not null,
	questionPass varchar2(100) not null,
	answerpass varchar2(100) not null,
	originalfilename varchar2(100),
	savefilename varchar2(100),
	enrolldate varchar2(50) not null,
	withdrawal_date varchar2(50) not null,
	university varchar2(50) not null,
	hopesubject varchar2(50) not null,
	hopegender varchar2(50) not null,
	availablestudent varchar2(50) not null,
	availableday varchar2(50) not null,
	availabletime varchar2(50) not null,
	intro varchar2(1000) not null
 )
  create table wmt_student(
   studentid varchar2(50) primary key,
   name varchar2(50) not null,
   password varchar2(50) not null,
   gender varchar2(50) not null,
   tel varchar2(50) not null,
   hoperegion varchar2(50) not null,
   email varchar2(50) not null,
   questionpass varchar2(100) not null,
   answerpass varchar2(100) not null,
   enrolldate varchar2(50) not null,
   withdrawaldate varchar2(50) not null,
   hopesubject varchar2(150) not null,
   zipcode varchar2(10) not null,
   codeaddress varchar2(200) not null,
   subaddress varchar2(150) not null
   
  )

  insert into WMT_teacher values('java','아이유','1111','여','1234','서울','iu@naver.com','할아버지 성함?','아이유할배',
  'iu','new iu','0619','0620','중앙대','수학','여','중3','월','3시','안냥');
  
 insert into WMT_student values('java2','수지','1111','여','1234','분당','suzi@naver.com','할아버지 성함?','수지할배',
  '0619','0620','수학','20','성남시 분당구', '703동 502호');

insert into WMT_student values('admin','관리자','1234','남','1234','분당','suzi@naver.com','할아버지 성함?','수지할배',
  '0619','0620','수학','20','성남시 분당구', '703동 502호');

create table WMT_admin(
	adminid varchar2(50) primary key, 
	name varchar2(50) not null,
	password varchar2(50) not null
)
insert into WMT_admin(adminid,name,password) values('admin','관리자','1234');

select count(*) from wmt_student where studentid='asf' union
 		select count(*) from wmt_teacher where teacherid='suzi'

select studentid, name, gender,hoperegion, hopesubject, grade  from (
	select studentid,name,gender,hoperegion,hopesubject, grade, ceil(rownum/10) as page from (
	select s.studentid as studentid, s.name as name, c1.code_name as gender,c2.code_name as hoperegion, 
	c3.code_name as hopesubject, c4.code_name as grade 
	from WMT_STUDENT s, code_master c1, code_master c2, code_master c3, code_master c4
	where s.withdrawaldate is null and s.gender=c1.code_code and s.hoperegion=c2.code_code 
	and s.hopesubject=c3.code_code and s.grade=c4.code_code order by name
	)
	) where page=1

select * from WMT_STUDENT;

select s.studentid as studentid, s.name as name, c.code_name as gender,c.code_name as hoperegion, 
	c.code_name as hopesubject, c.code_name as grade 
	from WMT_STUDENT s, code_master c
	where s.gender=c.code_code and s.hoperegion=c.code_code and s.hopesubject=c.code_code and s.grade=c.code_code

	select teacherid as id, name, gender,hoperegion, hopesubject, university, availablestudent, availableday  from (
	select teacherid,name,gender,hoperegion,hopesubject, university, availablestudent, availableday, ceil(rownum/10) as page from (
		select t.teacherid as teacherid, t.name as name, c1.code_name as gender,c2.code_name as hoperegion, c3.code_name as hopesubject, t.university, c4.code_name as availablestudent, t.availableday
		from WMT_Teacher t, code_master c1,  code_master c2,  code_master c3,  code_master c4
		where t.withdrawaldate is null and t.gender=c1.code_code and t.hoperegion=c2.code_code and t.hopesubject=c3.code_code and t.availablestudent=c4.code_code
		order by name
		)
	) where page=1
	
	select seq,zipcode,sido,gugun,dong,bunji from addresscode where gugun='군산시'
	
	
		select studentid,name,gender,hoperegion,hopesubject, grade, ceil(rownum/'2') as page from (
		select s.studentid as studentid, s.name as name, c1.code_name as gender,c2.code_name as hoperegion, c3.code_name as hopesubject, c4.code_name as grade
		from WMT_STUDENT s, code_master c1,  code_master c2,  code_master c3,  code_master c4
		where s.withdrawaldate is null and s.gender=c1.code_code and s.hoperegion=c2.code_code and s.hopesubject=c3.code_code and s.grade=c4.code_code
		order by hoperegion
		)
   select s.studentid as studentid, s.name as name, c1.code_name as gender,c2.code_name as hoperegion, c3.code_name as hopesubject, c4.code_name as grade
		from WMT_STUDENT s, code_master c1,  code_master c2,  code_master c3,  code_master c4
		where s.studentid='student2' and s.gender=c1.code_code and s.hoperegion=c2.code_code and s.hopesubject=c3.code_code and s.grade=c4.code_code
		
select teacherid as id, name, gender, tel, email, enrolldate, hoperegion, hopesubject, university, availablestudent, availableday, savefilename from (
	select teacherid, name, gender, tel, email, enrolldate, hoperegion, hopesubject, university, availablestudent, availableday, savefilename, ceil(rownum/3) as page from (
	select 
	t.teacherid as teacherid,
	t.name as name, 
	c1.code_name as gender, 
	t.tel as tel, 
	t.enrolldate as enrolldate, 
	t.email as email, 
	c2.code_name as hoperegion,
	c3.code_name as hopesubject, 
	t.university as university, 
	c4.code_name as availablestudent, 
	t.availableday as availableday, 
	t.savefilename as savefilename
	from wmt_teacher t, 
	code_master c1,  
	code_master c2,  
	code_master c3,  
	code_master c4
	where t.withdrawaldate is null and t.gender=c1.code_code and t.hoperegion=c2.code_code 
	and t.hopesubject=c3.code_code and t.availablestudent=c4.code_code order by t.enrolldate
	)
	) where page=1
	
	select t.teacherid as id, t.name as name, c1.code_name as gender,
    		  c2.code_name as hoperegion, c3.code_name as hopesubject,
    		  t.tel as tel, t.email as email, c4.code_name as questionpass, t.answerpass, t.savefilename as savefilename,
    		  t.enrolldate, t.university, t.hopegender, c5.code_name as availablestudent,t.availableday,t.availabletime,t.intro
	from WMT_Teacher t, code_master c1,  code_master c2,  code_master c3,
	        code_master c4,  code_master c5
	where t.teacherid='java' and t.gender=c1.code_code and t.hoperegion=c2.code_code
	         and t.hopesubject=c3.code_code and t.questionpass=c4.code_code
	         and t.availablestudent=c5.code_code
	